﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using DocumentFormat.OpenXml.Spreadsheet;
using AutoTestingFramework.Utilities.DataProviders;

namespace AutoTestingFramework.Utilities
{
    public class ExcelHelper : AutoTestingFramework.Utilities.DataProviders.IDataReader
    {
        string _ConnectionString;
        string _SheetName;
        public bool IsConnectionOpen
        {
            get 
            {
                if (objExcel != null)
                    return true;
                else
                    return false;
            }
        }
        public string TableName
        {
            get
            {
                return _SheetName;
            }
            set
            {
                _SheetName = value;
                if (this.IsConnectionOpen)
                    objExcel.SelectWorkSheetByName(_SheetName);
                else
                    throw (new Exception("Excel has not been initialized"));
            }
        }

       
        OpenXmlExcelHelper objExcel;
        public string ConnectionString
        {
            get { return _ConnectionString; }
            set { _ConnectionString = value; }
        }
        uint _Seek;

        public uint Seek
        {
            get { return _Seek; }
            set { _Seek = value; }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="FullyQualifiedFileName">Fully qualified name of the excel</param>
        /// <param name="SheetName">The default sheet name which will be loaded, default is null</param>
        public ExcelHelper(string FullyQualifiedFileName, string SheetName)
        {
            _ConnectionString = FullyQualifiedFileName;
            objExcel = new OpenXmlExcelHelper();
            this._SheetName = SheetName;
        }

        public void Open()
        {
            objExcel.ExcelName(ConnectionString, false);
            TableName = TableName;//first time the sheet name is stored in _SheetName referred to by TableName
        }

        public bool Next()
        {
            try
            {
                if (objExcel.NumberOfRowsUsed(TableName) > _Seek)
                {
                    _Seek++;
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                
                throw ex;

            }
        }

        public string[] GetPresentRowData()
        {
            DocumentFormat.OpenXml.Spreadsheet.Row rw = objExcel.GetRow(Seek);
            var list = from c in rw.Elements<Cell>()
                       select c.CellReference.Value;
            return list.ToArray();
        }

        public string GetCellData(int Column, int Row)
        {
           return objExcel.GetCellData(Column, Row);
        }

        public string GetCellData(string TestCaseId, int Column, int RowSeek)
        {
            throw new NotImplementedException();
        }

        public DataTable GetDataInDataSet(int ColumnBeginIndex, int ColumnEndIndex, int BeginRowIndex, int EndRowIndex)
        {
            return objExcel.GetDataInDataSet(ColumnBeginIndex, ColumnEndIndex, BeginRowIndex, EndRowIndex, false);
        }
        public void Close()
        {
            objExcel.Close();
        }       
    }
}
